package com.scs.application.core.export.service;

import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.scs.application.core.exception.BusinessException;
import com.scs.application.core.export.dto.ImportColumnDTO;
import com.scs.application.core.utils.*;
import com.scs.application.modules.base.util.AfterImport;
import com.scs.application.modules.report.service.ConfigService;
import com.scs.application.modules.sys.entity.Log;
import com.scs.application.modules.sys.enums.BusType;
import com.scs.application.modules.sys.enums.LogType;
import com.scs.application.modules.sys.properties.SystemProperties;
import com.scs.application.modules.sys.service.LogService;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

@Slf4j
@Component
public class ImportService {
    protected static final Logger logger = LoggerFactory.getLogger(ImportService.class);
    @Autowired
    private  JdbcTemplate jdbcTemplate;

    @Autowired
    private SystemProperties systemProperties;

    private static  String spd2Error="spd2数据源初始化失败，请检查yml配置文件节点是否配置正确。<br>对应配置节点： system.spd2<br>可参考application-prod.yml相关节点配置";

    /**
    * @Description： spd2.0 spd数据库数据源
    */
    private static  JdbcTemplate jdbcTemplateSpd2;

    /**
     * @Description： spd2.0 spd_sys数据库数据源
     */
    private static  JdbcTemplate jdbcTemplateSpdSys2;

    @Autowired
    @Lazy
    private  ConfigService configService;

    @Autowired
    @Lazy
    private LogService logService;

    private static ImportService services;
    @PostConstruct
    public void init() {
        services = this;
        services.jdbcTemplate = this.jdbcTemplate;
        services.configService = this.configService;
        services.logService = this.logService;
        services.systemProperties = this.systemProperties;
    }



    /**
     * @Description： excel数据添加到临时表中
     */
    public static String addTmpTableDataFromExcel(String filePath,String tempTableName,String importStamp, JSONArray columnsArr,JSONObject dataViewOb) throws Exception {
        int opAll=0,opSuccess=0,opError=0;
        ArrayList<String[]> arrLst = UtilExcelPoi.getListFromXls(filePath, 0);//获取第一个sheet页
        if (arrLst.size() > 500000) {
            throw new BusinessException("对不起, 导入失败! 超过导入数据量最多500000条限制! 请分多次导入。");
        }
        List<String> arrHeadLst = Arrays.asList(arrLst.get(0));//获取Excel表格中的第一行--表头列表
        List<String> fieldList = columnsArr.stream().map(column -> StringUtils.camelToUnderline(((JSONObject) column).getString("name"), 1)).collect(Collectors.toList());//获取dataview中定义的字段列表
        List<String> titleList = columnsArr.stream().map(column -> ((JSONObject) column).get("title") + "").collect(Collectors.toList());//dataview中定义的字段显示名称标题


        AtomicInteger titleIndex = new AtomicInteger();
        //1、根据导入模板配置，校验表头--如果dataView定义的标题名和表格中的第一行的表头是否匹配
        titleList.stream().forEach(title -> {
            AtomicInteger index=new AtomicInteger(0);
//            Arrays.stream(inputArray).map(x->x+index.getAndIncrement());
            boolean find = arrHeadLst.stream().filter(tmp -> {
                index.getAndIncrement();
                return tmp.indexOf(title) != -1;
            }).findFirst().isPresent();

            if (!find){
                throw new BusinessException("导入失败，原因：导入数据列【%s】在导入模板中不存在", title);
            }
            columnsArr.getJSONObject(titleIndex.get()).put("index", index.get());
            titleIndex.getAndIncrement();
        });


//        arrHeadLst.stream().forEach(headName -> {
//            if (!titleList.contains(headName)) throw new BusinessException("导入失败，原因：导入数据列【%s】在导入模板中不存在", headName);
//        });

        //11 添加excel索引
//        for (int i = 0; i < columnsArr.size(); i++) {
//            String fileName = StringUtils.camelToUnderline(columnsArr.getJSONObject(i).getString("name"), 1);
//            String fileName = StringUtils.camelToUnderline(columnsArr.getJSONObject(i).getString(title), 1);
//            columnsArr.getJSONObject(i).put("index", fieldList.indexOf(fileName));
//        }

        //2、导入临时表数据，不存在或者字段跟导入模板配置不一致，则删除并创建表
        String sql = "SELECT count(1) FROM information_schema.TABLES WHERE table_schema=(select database()) and table_type = 'BASE TABLE' AND table_name = '" + tempTableName + "'";
        long find = 0;
        try {
            find = services.jdbcTemplate.queryForObject(sql, Long.class);
        }catch (Exception e) {

        }

        //临时表存在时，检查临时表字段是否发生了变化
        boolean tableCHange = false;
        if (find > 0) {
            Map mapTempTable = null;
            try {
                mapTempTable = services.jdbcTemplate.queryForMap(" select * from " + tempTableName + "  limit 1   ");
            } catch (Exception e) {
                logger.error("ImportService.addTmpTableDataFromExcel.find.error", e);
            }
            //有数据时再进行对比
            if (mapTempTable != null) {
                //临时表多字段的
                //先判断长度是否相等
                tableCHange = (mapTempTable.size() - 4 != fieldList.size());
                if (!tableCHange) {
                    tableCHange = mapTempTable.keySet().stream()
                            .filter(keyFilter -> !"import_stamp".equals(keyFilter) && !"import_result".equals(keyFilter) && !"import_remark".equals(keyFilter) && !"id".equals(keyFilter)).
                            anyMatch(key -> fieldList.stream().noneMatch(key2 -> key2.equals(key)));
                }

            }
            //表结构变化或者临时表没数据的，直接删除掉就可以
            if (tableCHange || mapTempTable == null) {
                tableCHange = true;
                services.jdbcTemplate.execute("drop table " + tempTableName);
            }
        }
        if (find < 1 || tableCHange) {//临时表不存在或者表结构变化，直接新建该临时表
            sql = "";
            sql += " create table " + tempTableName + "  ";
            sql += " as   ";
            sql += " select * from ( select '' as id,'' as import_stamp,'' as import_result,'' as import_remark," + StringUtils.join(fieldList, ",") + "  from " + dataViewOb.get("baseTable") + ") t where 1=0   ";
            services.jdbcTemplate.execute(sql);

            //修改扩展字段长度
            services.jdbcTemplate.execute("alter table " + tempTableName + " modify column import_stamp varchar(50) ");
            services.jdbcTemplate.execute("alter table " + tempTableName + " modify column import_result varchar(10) ");
            services.jdbcTemplate.execute("alter table " + tempTableName + " modify column import_remark varchar(100) ");
            services.jdbcTemplate.execute("alter table " + tempTableName + " modify column id varchar(100) ");

            JSONArray importPkFields = dataViewOb.getJSONArray("importPkFields");
            /**
            * @Description：不要索引了，兼容更好
            * @Author wyt
            * @Date 2023-09-09

            MySqlSysHelper.addIndex(services.jdbcTemplate, tempTableName, EntityIndexType.PRIMARY, "pk", "id");

            //普通索引

            if (importPkFields != null && importPkFields.size() > 0) {
                List<String> importPkFieldsNew = new ArrayList<String>();
                for (int j = 0; j < importPkFields.size(); j++) {
                    importPkFieldsNew.add(StringUtils.camelToUnderline(importPkFields.getString(j), 1));
                }
                MySqlSysHelper.addIndex(services.jdbcTemplate, tempTableName, EntityIndexType.INDEX, "ind", StringUtils.join(importPkFieldsNew, ","));
            }
             */
            //为了数据兼容，修改除了索引字段外其余的为字符型
            fieldList.stream().filter(fieldTmp -> importPkFields !=null && !importPkFields.contains(fieldTmp)).forEach(field -> {
                if (field.contains("flag")) { //修改为整型
                    services.jdbcTemplate.execute("alter table " + tempTableName + " modify column "+field+" int ");
                } else{
                    services.jdbcTemplate.execute("alter table " + tempTableName + " modify column "+field+" varchar(200) ");
                }
            });
        }

        //3、根据导入模板配置，校验临时表数据，并进行结果标记
        int rowDataBegin = 1, nRowCount = arrLst.size();//导入数据是从Excel表格的第三行开始的
        String[] arrRow = null;
        String cellVal = null;
        int nullNum=0;
        List<String> insertList = new ArrayList<String>();
        for (int nRow = rowDataBegin; nRow < nRowCount; nRow++) {
            arrRow = arrLst.get(nRow);//Excel导数据的每行数据列

            List<String> fieldNameList = new ArrayList<String>();
            List<String> fieldValueList = new ArrayList<String>();

            //列
            nullNum=0;
            for (Object object : columnsArr) {
                JSONObject column = (JSONObject) object;//数据视图dataView定义的列信息的JSON数组

                String fileName = StringUtils.camelToUnderline(column.getString("name"), 1);//将数据视图的列字段驼峰转成下划线
                fieldNameList.add(fileName);//拼装字段名放在集合中

                cellVal = UtilCommon.getStr(arrRow[column.getInteger("index")],"");//获取对应的Excel数据行中对应的单元格中的数据值
                if (StringUtils.isBlank(cellVal)) nullNum++;//空列的个数
                if (fileName.contains("flag")) {//如果视图的字段名中包含flag
                    cellVal = UtilCommon.getStr(cellVal,"-1");
                }
                fieldValueList.add("'" + cellVal + "'");//拼装字段的值放在集合中
            }
            //该行全空则忽略
            if (nullNum == fieldValueList.size()) continue;
            opAll++;

            //额外字段
            fieldNameList.add("import_stamp");
            fieldNameList.add("import_result");
            fieldNameList.add("import_remark");
            fieldNameList.add("id");

            fieldValueList.add("'"+importStamp+"'");
            fieldValueList.add("'未导入'");
            fieldValueList.add("''");
            fieldValueList.add("'" + UUID.randomUUID() + "'");
            insertList.add(" INSERT INTO " + tempTableName + " (" + StringUtils.join(fieldNameList, ",") + ") VALUES (" + StringUtils.join(fieldValueList, ",") + ")");
            if (insertList.size() == 100) {
                opSuccess += insertList.size();
                services.jdbcTemplate.execute(StringUtils.join(insertList, ";"));
                insertList = new ArrayList<String>();
            }
        }

        if (insertList.size() > 0) {
            opSuccess += insertList.size();
            services.jdbcTemplate.execute(StringUtils.join(insertList, ";"));
        }
        logger.info("ImportService.addTmpTableData.结束");
        return "导入临时表结束，总数量："+opAll+"，成功："+opSuccess+"，失败："+opError;
    }


    /**
     * @Description： 将临时表数据导入到正式表中
     */
    public static String addTableDataFromTmpTable(String filePath,String tempTableName,String importStamp, JSONArray columnsArr,JSONObject dataViewOb) throws Exception {
        BusinessException.throwErrorIf(true, "此方法已经废弃");
        String baseTable = dataViewOb.getString("baseTable");//获取基础表名
        boolean importTransactional = Boolean.valueOf(UtilCommon.getStr(dataViewOb.getString("importTransactional"),"false"));//是否全量导入
        String tablePrefix = dataViewOb.getString("tablePrefix");//获取表名前缀
        String entityName = StringUtils.underline2Camel(baseTable.replace(tablePrefix,""),false);//拿到对应的实体类名
        String entityClass = dataViewOb.getString("basePackageName")+"."+dataViewOb.getString("moduleName")+".entity."+entityName;//拼装对应的全路径名
        int opAll=0,opSuccess=0,opError=0;
        //1、将临时表校验通过的数据添加到正式表--先将没有导入正式表全部成功的数据全部取出来
        List<Map<String, Object>> dataList = services.jdbcTemplate.queryForList("select * from "+tempTableName +" where import_result!='成功' and  import_stamp ='"+importStamp+"'");
        List<Object> saveList= new ArrayList<>();
        List<String> updateTmpTableSqlList = new ArrayList<String>();
        String returnMsg ="";

        if (dataList != null){
            opAll=dataList.size();
            Map<String,JSONObject>  columnsMap = new HashMap<String,JSONObject>();
            columnsArr.forEach(object -> {
                JSONObject jsonObject = (JSONObject)object;
                String fileName = StringUtils.camelToUnderline(jsonObject.getString("name"),1);
                columnsMap.put(fileName,jsonObject);
            });
            List<String> enum1=Arrays.asList("0","1","否","是");
            for(int i=0;i<dataList.size();i++){
                Map<String, Object> dataOb = dataList.get(i);
                String id = dataOb.get("id")+"";

                //数据校验
                final String[] import_result = {"成功"};
                final String[] import_remark = {""};
                for (String key : dataOb.keySet()){//校验每个字段的数据，有一个字段失败该条就失败
                    if (!columnsMap.containsKey(key)) continue;
                    JSONObject column= columnsMap.get(key);
                    String value = dataOb.get(key)+"";
                    String importDataType = UtilCommon.getStr(column.getString("importDataType"),"");
                    String title = UtilCommon.getStr(column.getString("title"),"");
                    String importDataCheckType = UtilCommon.getStr(column.getString("importDataCheckType"),"");

                    //导入数据类型
                    switch (importDataType){
                        case "string": //字符串
                            value = UtilCommon.getStr(value,null);
                            break;
                        case "number": //数字
                            value = UtilCommon.getStr(value,"0");
                            break;
                        case "date": //日期
                            value = UtilCommon.getStr(value,"null");
                            break;
                    }


                    //导入数据类型校验
                    switch (importDataCheckType){
                        case "require": //必须的(不可以为空，同时数字不可以为0)
                            if(importDataType.equals("string") && StringUtils.isBlank(value)) {
                                import_remark[0] ="不可以为空";
                            }else  if(importDataType.equals("number") && value.equals("0")) {
                                import_remark[0] ="不可以为空，也不可以为0";
                            }
                            break;
                        case "number1": //正整数(必须为大于0的整数)
                            if (!UtilNum.isPureDigital(value)){
                                import_remark[0] ="必须为大于0的整数";
                            }
                            break;
                        case "number2": //正数(必须为大于0的正数)
                            if (StringUtils.isBlank(value) || UtilNum.getDouble(value) < 0){
                                import_remark[0] ="必须为大于0的正数";
                            }
                            break;
                        case "enum1": //枚举：0、1、是、否
                            if (!enum1.contains(value)){
                                import_remark[0] ="枚举：0、1、是、否";
                            }
                            break;
                        case "date1": //日期(年-月-日)，可以为空
                            if (StringUtils.isNotBlank(value)
                                    && !DateUtils.isValidDate(value,"yyyy-MM-dd")
                                    && !DateUtils.isValidDate(value,"yyyy/MM/dd")
                            ){
                                import_remark[0] ="日期格式非法";
                            }
                            break;
                        case "date2": //日期(年-月-日)，不可以为空
                            if (StringUtils.isBlank(value)){
                                import_remark[0] ="日期不可以为空";
                            } else if ( !DateUtils.isValidDate(value,"yyyy-MM-dd")
                                    && !DateUtils.isValidDate(value,"yyyy/MM/dd")){
                                import_remark[0] ="日期格式非法";
                            }
                            break;
                        case "date3": //日期(年-月-日 时:分:秒)，可以为空
                            if (StringUtils.isNotBlank(value)
                                    && !DateUtils.isValidDate(value,"yyyy-MM-dd HH:mm:ss")
                                    && !DateUtils.isValidDate(value,"yyyy/MM/dd HH:mm:ss")
                            ){
                                import_remark[0] ="日期格式非法";
                            }
                            break;
                        case "date4": //日期(年-月-日 时:分:秒)，不可以为空
                            if (StringUtils.isBlank(value)){
                                import_remark[0] ="日期不可以为空";
                            } else if ( !DateUtils.isValidDate(value,"yyyy-MM-dd HH:mm:ss")
                                    && !DateUtils.isValidDate(value,"yyyy/MM/dd HH:mm:ss")){
                                import_remark[0] ="日期格式非法";
                            }
                            break;
                    }

                    if (StringUtils.isNotBlank(import_remark[0])){
                        import_remark[0]="字段【"+title+"】校验失败，"+import_remark[0];
                        import_result[0] ="失败";
                        break;
                    }
                }
                if (import_result[0].equals("失败")){//这种判断可能会有漏网之鱼，比如校验通过了实际插入时却失败了

                    if (importTransactional) {
                        opSuccess = 0;
                        opError=opAll;
                        returnMsg = "导入正式表结束(当前导入模板为全量导入(即有失败则全部失败))，总数量："+opAll+"，成功："+opSuccess+"，失败："+opError;
                        updateTmpTableSqlList = new ArrayList<String>();
                        updateTmpTableSqlList.add(" update "+tempTableName+" set import_result ='"+import_result[0]+"',import_remark='"+import_remark[0]+"' where id ='"+id+"'  ");
                        break;
                    }
                    opError++;
                    updateTmpTableSqlList.add(" update "+tempTableName+" set import_result ='"+import_result[0]+"',import_remark='"+import_remark[0]+"' where id ='"+id+"'  ");
                }else {
                    dataOb.put("id",null);
                    Object ob = JSONObject.toJavaObject(JSONObject.parseObject(JSON.toJSONString(dataOb)),Class.forName(entityClass));
                    saveList.add(ob);
                    opSuccess++;
                    updateTmpTableSqlList.add(" update "+tempTableName+" set import_result ='"+import_result[0]+"',import_remark='"+import_remark[0]+"' where id ='"+id+"'  ");
                }
            }
        }

        //执行批量保存方法
        if (!(importTransactional && opError > 0) && saveList.size()> 0   ){
            try {
                services.configService.saveBatchImport(Class.forName(entityClass),saveList,saveList.size());
            } catch (Exception e) {
                logger.info("ImportService.addTableDataFromTmpTable.error",e);
                throw new BusinessException("批量保存失败，原因:%s",e.getMessage());
            }
        }

        //校验失败的数据
        if (updateTmpTableSqlList.size()> 0){
            try {
                services.jdbcTemplate.execute(StringUtils.join(updateTmpTableSqlList,";"));
            } catch (Exception e) {
                logger.info("ImportService.addTableDataFromTmpTable.updateTmpTableSqlList",e);
                throw new BusinessException("更新临时表状态失败，原因:%s",e.getMessage());
            }
        }

        //2、执行导入后置方法
        //有后置方法
        if(!(importTransactional && opError > 0) && StringUtils.isNotBlank(dataViewOb.getString("afterImport"))){
            try {
                String res = SpringUtils.invokeMethod(AfterImport.class,dataViewOb.getString("afterImport")).toString();
            } catch (Exception e) {
                logger.info("ImportService.addTableDataFromTmpTable.error",e);
                throw new BusinessException("执行导入后置方法%s失败，原因:%s",dataViewOb.getString("afterImport"),e.getMessage());
            }
        }
        if (StringUtils.isBlank(returnMsg)) returnMsg ="导入正式表结束，总数量："+opAll+"，成功："+opSuccess+"，失败："+opError;
        return returnMsg;
    }

    /**
     * @Description： 初始化2.0 spd_sys
     */
    private static void initSpd2jdbcTemplate() {
        if (services.jdbcTemplateSpd2 != null) return;
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        if (services.systemProperties == null || StringUtils.isBlank(services.services.systemProperties.getSpd2().getUsername())) throw new BusinessException(spd2Error);
        dataSource.setUrl(services.systemProperties.getSpd2().getSpdurl());
        dataSource.setUsername(services.systemProperties.getSpd2().getUsername());
        dataSource.setPassword(services.systemProperties.getSpd2().getPassword());
        dataSource.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        services.jdbcTemplateSpd2 = new JdbcTemplate(dataSource);
    }

    /**
     * @Description： 初始化2.0 spd
     */
    private static void initSpdSys2jdbcTemplate() {
        if (services.jdbcTemplateSpdSys2 != null) return;
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        if (services.systemProperties == null || StringUtils.isBlank(services.systemProperties.getSpd2().getUsername())) throw new BusinessException(spd2Error);
        dataSource.setUrl(services.systemProperties.getSpd2().getSpdsysurl());
        dataSource.setUsername(services.systemProperties.getSpd2().getUsername());
        dataSource.setPassword(services.systemProperties.getSpd2().getPassword());
        dataSource.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        services.jdbcTemplateSpdSys2 = new JdbcTemplate(dataSource);
    }


    /**
     * @Description： 数据视图-批量同步数据
     */
    public static String syncBatchFromDataview(String[] viewKeysArr) throws Exception {
        initSpd2jdbcTemplate();
        initSpdSys2jdbcTemplate();
        String result = "";
        boolean isError = false;
        Log log=Log.getLog(BusType.OtherType.type01, LogType.success,UserUtils.currentUser().getName(),"");
        Exception eOut=null;
        for(String viewKey : viewKeysArr) {
            try {
                result+=syncOneFromDataview(viewKey);
            }catch (Exception e) {
                eOut=e;
                logger.error("syncBatchFromDataview.同步视图执行失败，同步视图：{}",viewKey,e );
                result+="syncBatchFromDataview.同步视图执行失败，同步视图："+viewKey+"，失败原因："+(e != null ? e.getMessage() : "未知原因");
                isError = true;
            }
        }
        log.setMsg(result);
        if (isError) {
            log.setLogType(LogType.error);
            services.logService.save(log);
            throw new BusinessException(result);
        }
        services.logService.save(log);
        return result;
    }

    /**
     * @Description： 数据视图-单个同步数据
     */
    public static String syncOneFromDataview(String viewKey) throws Exception {
        String result = "";
        int execNum = 100;
        String viewString = CacheUtils.get(CacheUtils.DATAVIEW_CACHE,viewKey)+"";
        JSONObject dataViewOb = JSONObject.parseObject(viewString);
        //视图名称
        String viewName = dataViewOb.getString("viewName");

        //目标表主键
        String primaryKey = dataViewOb.getString("primaryKey");
        //数据来源
        String dataSourceType = dataViewOb.getString("dataSourceType");
        if (StringUtils.isBlank(dataSourceType)) throw new BusinessException("请选择数据源");

        //数据源
        String sqlFrom = dataViewOb.getString("sqlFrom");
        //基础表
        String baseTable = dataViewOb.getString("baseTable");

        logger.info("syncBatchFromDataview.同步视图开始执行，同步视图：{}，数据源：{}，目标表：{}",viewKey,sqlFrom,baseTable );

        //同步列配置
        List<ImportColumnDTO> importColumnDTOList = JSONArray.parseArray(dataViewOb.getString("columns"), ImportColumnDTO.class);

        //字段关系
        Map<String,ImportColumnDTO>  columnsMap = new HashMap<String,ImportColumnDTO>();
        Map<String,String>  columnsNullTypeMap = new HashMap<String,String>();
        importColumnDTOList.forEach(importColumnDTO -> {
            if (StringUtils.isNotBlank(importColumnDTO.getNullType())) columnsNullTypeMap.put(importColumnDTO.getNameFrom(),importColumnDTO.getNullType());
            columnsMap.put(importColumnDTO.getName(),importColumnDTO);
        });

        //主键关系
        ImportColumnDTO importColumnPkDTO = importColumnDTOList.stream().filter(tmp -> tmp.getName().equals(primaryKey)).findFirst().get();
        if (importColumnPkDTO == null) {
            return "同步视图【"+viewName+"】执行失败，原因：缺少主键对应关系<br>";
        }

        //校验原始表主键是否重复
        long find = 0;
        try {
            /**  type1： spd2.0 spd数据库， type2：spd2.0 spd_sys数据库
            */
            switch (dataSourceType) {
                case "type1":
                    find = services.jdbcTemplateSpd2.queryForObject(" select COUNT(1) from ("+sqlFrom+")t GROUP BY "+importColumnPkDTO.getNameFrom()+" HAVING COUNT(1) > 1 ",Long.class);
                    break;
                case "type2":
                    find = services.jdbcTemplateSpdSys2.queryForObject(" select COUNT(1) from ("+sqlFrom+")t GROUP BY "+importColumnPkDTO.getNameFrom()+" HAVING COUNT(1) > 1 ",Long.class);
                    break;
            }

        }catch (EmptyResultDataAccessException e) {
        }
        if (find >0) {
            return "同步视图【"+viewName+"】执行失败，原因：数据源主键重复，重复数量："+find+"<br>";
        }

        List<String> insertSqlList = new ArrayList<String>();
        List<String> updateSqlList = new ArrayList<String>();
        List<String> fieldNameInsertList;
        List<String> fieldValueList;
        List<String> fieldNameUpdateList;
        int numInsert = 0,numUndate=0;

        //目标表主键列表
        List<String> baseTablePkList = services.jdbcTemplate.queryForList("select "+importColumnPkDTO.getName()+" from "+baseTable,String.class);

        //数据源数据
        List<Map<String, Object>> dataList=null;
        /**  type1： spd2.0 spd数据库， type2：spd2.0 spd_sys数据库 【与前端数据视图配置类型保持一致】
         */
        switch (dataSourceType) {
            case "type1":
                dataList = services.jdbcTemplateSpd2.queryForList(sqlFrom);
                break;
            case "type2":
                dataList = services.jdbcTemplateSpdSys2.queryForList(sqlFrom);
                break;
        }

        if (dataList == null || dataList.size() < 1) return "同步视图【"+viewName+"】执行结束，数据源没有任何数据<br>";

        String value ="",toFieldName;
        for(int i=0;i<dataList.size();i++){
            Map<String, Object> dataOb = dataList.get(i);
            fieldNameInsertList = new ArrayList<String>();
            fieldValueList = new ArrayList<String>();
            fieldNameUpdateList = new ArrayList<String>();
            //主键
            String pkValue = String.valueOf(dataOb.get(importColumnPkDTO.getNameFrom()));

            //主键不存在则新增
            boolean isAdd = baseTablePkList.stream().noneMatch(pk -> pk.equals(pkValue));

            for (String keyTo : columnsMap.keySet()){
                ImportColumnDTO importColumnDTO = columnsMap.get(keyTo);
                if (importColumnDTO ==null || StringUtils.isBlank(importColumnDTO.getNameFrom()) || StringUtils.isBlank(importColumnDTO.getName())) continue;
                //数据源字段不存在则忽略
                if (importColumnDTOList.stream().noneMatch(tmp -> importColumnDTO.getNameFrom().equals(tmp.getNameFrom()))) continue;
                value = UtilCommon.getStr(String.valueOf(dataOb.get(importColumnDTO.getNameFrom())),"");

                //为空时取值
                if (StringUtils.isBlank(value) && columnsNullTypeMap.get(importColumnDTO.getNameFrom()) != null) {
                    /**
                     { value: 'type1', label: '为空时取0' },
                     { value: 'type2', label: '为空时取1970-01-01，一般为效期开始时间' },
                     { value: 'type3', label: '为空时取2099-01-01，一般为效期结束时间' },
                     { value: 'type4', label: '为空时取主键' },
                     【与前端数据视图配置类型保持一致】
                     */
                    switch (columnsNullTypeMap.get(importColumnDTO.getNameFrom())) {
                        case "type1":
                            value = "0";
                            break;
                        case "type2":
                            value = "1970-01-01";
                            break;
                        case "type3":
                            value = "2099-01-01";
                            break;
                        case "type4":
                            value = pkValue;
                            break;
                    }
                }

                toFieldName= StringUtils.camelToUnderline(importColumnDTO.getName(),1);
                if (toFieldName.equals("lot")) {
                    value =value.replaceAll("\\\\","/");
                }
                if (isAdd){
                    fieldNameInsertList.add(toFieldName);
                    fieldValueList.add("'" + value + "'");
                }else {
                    fieldNameUpdateList.add(toFieldName+" = '"+value+"' ");
                }
            }

            if (isAdd) {
                insertSqlList.add(" INSERT INTO " + baseTable + " (" + StringUtils.join(fieldNameInsertList, ",") + ") VALUES (" + StringUtils.join(fieldValueList, ",") + ")");
            } else {
                updateSqlList.add(" update " + baseTable+" set " + StringUtils.join(fieldNameUpdateList, ",") + " where " + importColumnPkDTO.getName() + " = '"+pkValue+"' " );
            }

            if (insertSqlList.size() == execNum) {
                numInsert += insertSqlList.size();
                services.jdbcTemplate.execute(StringUtils.join(insertSqlList, ";"));
                insertSqlList = new ArrayList<String>();
            }

            if (updateSqlList.size() == execNum) {
                numUndate += updateSqlList.size();
                services.jdbcTemplate.execute(StringUtils.join(updateSqlList, ";"));
                updateSqlList = new ArrayList<String>();
            }
        }

        if (insertSqlList.size() > 0) {
            numInsert += insertSqlList.size();
            services.jdbcTemplate.execute(StringUtils.join(insertSqlList, ";"));
        }
        if (updateSqlList.size() > 0) {
            numUndate += updateSqlList.size();
            services.jdbcTemplate.execute(StringUtils.join(updateSqlList, ";"));
        }
        result+="同步视图【"+viewName+"】执行完毕，新增数量："+numInsert+"，更新数量:"+numUndate+"<br>";
        logger.info("syncBatchFromDataview.同步视图执行完毕，同步视图：{}，数据源：{}，目标表：{}，新增数量：{}，更新数量：{}",viewKey,sqlFrom,baseTable,numInsert,numUndate );
        return result;
    }


    /**
     * @Description： excel数据添加到临时表中
     */
    public static List<JSONObject> getTableDataFromExcel(String filePath,String tempTableName,String importStamp, JSONArray columnsArr,JSONObject dataViewOb) throws Exception {
        int opAll=0,opSuccess=0,opError=0;
        ArrayList<String[]> arrLst = UtilExcelPoi.getListFromXls(filePath, 0);//获取第一个sheet页
        if (arrLst.size() > 500000) {
            throw new BusinessException("对不起, 导入失败! 超过导入数据量最多500000条限制! 请分多次导入。");
        }
        List<String> arrHeadLst = Arrays.asList(arrLst.get(0));//获取Excel表格中的第一行--表头列表
        List<String> fieldList = columnsArr.stream().map(column -> StringUtils.camelToUnderline(((JSONObject) column).getString("name"), 1)).collect(Collectors.toList());//获取dataview中定义的字段列表
        List<String> titleList = columnsArr.stream().map(column -> ((JSONObject) column).get("title") + "").collect(Collectors.toList());//dataview中定义的字段显示名称标题


        AtomicInteger titleIndex = new AtomicInteger();
        //1、根据导入模板配置，校验表头--如果dataView定义的标题名和表格中的第一行的表头是否匹配
        titleList.stream().forEach(title -> {
            AtomicInteger index=new AtomicInteger(0);
//            Arrays.stream(inputArray).map(x->x+index.getAndIncrement());
            boolean find = arrHeadLst.stream().filter(tmp -> {
                index.getAndIncrement();
                return tmp.indexOf(title) != -1;
            }).findFirst().isPresent();

            if (!find){
                throw new BusinessException("导入失败，原因：导入数据列【%s】在导入模板中不存在", title);
            }
            columnsArr.getJSONObject(titleIndex.get()).put("index", index.get() - 1);
            titleIndex.getAndIncrement();
        });

        //3、根据导入模板配置，校验临时表数据，并进行结果标记
        int rowDataBegin = 1, nRowCount = arrLst.size();//导入数据是从Excel表格的第2行开始的
        String[] arrRow = null;
        String value = null;
        int nullNum=0;
        List<String> insertList = new ArrayList<String>();
        List<JSONObject> dataList = new ArrayList<>();
        Map<String,JSONObject>  columnsMap = new HashMap<String,JSONObject>();
        columnsArr.forEach(object -> {
            JSONObject jsonObject = (JSONObject)object;
            String fileName = StringUtils.camelToUnderline(jsonObject.getString("name"),1);
            columnsMap.put(fileName,jsonObject);
        });
        List<String> enum1=Arrays.asList("0","1","否","是");
        String errMsg = "";
        StringBuffer sbErr = new StringBuffer(),sbErrOne;
        for (int nRow = rowDataBegin; nRow < nRowCount; nRow++) {
            arrRow = arrLst.get(nRow);//Excel导数据的每行数据列

            List<String> fieldNameList = new ArrayList<String>();
            List<String> fieldValueList = new ArrayList<String>();
            JSONObject dataOne = new JSONObject();
            //列
            nullNum=0;
            sbErrOne = new StringBuffer();
            for (Object object : columnsArr) {
                errMsg = "";
                JSONObject column = (JSONObject) object;//数据视图dataView定义的列信息的JSON数组

                String fileName = StringUtils.camelToUnderline(column.getString("name"), 1);//将数据视图的列字段驼峰转成下划线
                fieldNameList.add(fileName);//拼装字段名放在集合中

                value = UtilCommon.getStr(arrRow[column.getInteger("index")],"");//获取对应的Excel数据行中对应的单元格中的数据值
                if (StringUtils.isBlank(value)) nullNum++;//空列的个数
                fieldValueList.add("'" + value + "'");//拼装字段的值放在集合中

                String importDataType = UtilCommon.getStr(column.getString("importDataType"),"");
                String title = UtilCommon.getStr(column.getString("title"),"");
                String importDataCheckType = UtilCommon.getStr(column.getString("importDataCheckType"),"");

                //导入数据类型
                switch (importDataType){
                    case "string": //字符串
                        value = UtilCommon.getStr(value,null);
                        break;
                    case "number": //数字
                        value = UtilCommon.getStr(value,"0");
                        break;
                    case "date": //日期
                        value = UtilCommon.getStr(value,"null");
                        break;
                }

                //导入数据类型校验
                switch (importDataCheckType){
                    case "require": //必须的(不可以为空，同时数字不可以为0)
                        if(importDataType.equals("string") && StringUtils.isBlank(value)) {
                            errMsg ="不可以为空";
                        }else  if(importDataType.equals("number") && value.equals("0")) {
                            errMsg ="不可以为空，也不可以为0";
                        }
                        break;
                    case "number1": //正整数(必须为大于0的整数)
                        if (!UtilNum.isPureDigital(value)){
                            errMsg ="必须为大于0的整数";
                        }
                        break;
                    case "number2": //正数(必须为大于0的正数)
                        if (StringUtils.isBlank(value) || UtilNum.getDouble(value) < 0){
                            errMsg ="必须为大于0的正数";
                        }
                        break;
                    case "enum1": //枚举：0、1、是、否
                        value= UtilCommon.getStr(value,"0");
                        if (NumberUtil.isDouble(value)) {
                            value = UtilNum.trimZero(value);
                        }

                        if (!enum1.contains(value)){
                            errMsg ="枚举：0、1、是、否";
                        }
                        break;
                    case "date1": //日期(年-月-日)，可以为空
                        if (StringUtils.isNotBlank(value)
                                && !DateUtils.isValidDate(value,"yyyy-MM-dd")
                                && !DateUtils.isValidDate(value,"yyyy/MM/dd")
                        ){
                            errMsg ="日期格式非法";
                        }
                        break;
                    case "date2": //日期(年-月-日)，不可以为空
                        if (StringUtils.isBlank(value)){
                            errMsg ="日期不可以为空";
                        } else if ( !DateUtils.isValidDate(value,"yyyy-MM-dd")
                                && !DateUtils.isValidDate(value,"yyyy/MM/dd")){
                            errMsg ="日期格式非法";
                        }
                        break;
                    case "date3": //日期(年-月-日 时:分:秒)，可以为空
                        if (StringUtils.isNotBlank(value)
                                && !DateUtils.isValidDate(value,"yyyy-MM-dd HH:mm:ss")
                                && !DateUtils.isValidDate(value,"yyyy/MM/dd HH:mm:ss")
                        ){
                            errMsg ="日期格式非法";
                        }
                        break;
                    case "date4": //日期(年-月-日 时:分:秒)，不可以为空
                        if (StringUtils.isBlank(value)){
                            errMsg ="日期不可以为空";
                        } else if ( !DateUtils.isValidDate(value,"yyyy-MM-dd HH:mm:ss")
                                && !DateUtils.isValidDate(value,"yyyy/MM/dd HH:mm:ss")){
                            errMsg ="日期格式非法";
                        }
                        break;
                }

                if (StringUtils.isNotBlank(errMsg)){
                    sbErrOne.append(StrUtil.format("字段{}，值【{}】校验失败，原因：{}<br>",column.getString("title"),value,errMsg));
//                    sbErr.append(StrUtil.format("第{}行，字段{}，值【{}】校验失败，原因：{}<br>",rowDataBegin + 1,column.getString("title"),value,errMsg));
                    continue;
                }
                dataOne.put(column.getString("name"),value);
            }
            //该行全空则忽略
            if (nullNum == fieldValueList.size()) continue;
            if (sbErrOne.length() < 1){
                opSuccess++;
            } else {
                opError++;
                sbErr.append(StrUtil.format("第{}行，{}<br>",nRow + 1,sbErrOne.toString()));
            }
            opAll++;

            dataList.add(dataOne);
        }
        if (sbErr.length() > 0){
            BusinessException.throwError(StrUtil.format("总数量{} 校验成功{} 校验失败{}<br>",opAll,opSuccess,opError) + sbErr.toString());
        }

        return dataList;
    }
}
